﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Drawing.Imaging
Public Class Frm_Gestion_Fourn
    Public str As String = ""
    Public num As Integer = 0
    Public modif As Integer = 0

    Public Sub vider()

        txt_nom.Clear()
        txt_prenom.Clear()
        txt_adresse.Clear()
        txt_mail.Clear()
        txt_nom_entrep.Clear()
        txt_nom_interl.Clear()
        txt_portabl.Clear()
        txt_fix.Clear()
        txt_fax.Clear()
        txt_remarq.Clear()
        Radio_partc.Checked = True
        Radio_entr.Checked = False
        If Comb_catégor.Items.Count > 0 Then
            Comb_catégor.SelectedIndex = 0
        End If
        If comb_ville.Items.Count > 0 Then
            comb_ville.SelectedIndex = 0
        End If
        Pic_photo.Image = My.Resources.photo
        txt_nom.Select()
        modif = 0
    End Sub

    Public Sub Afficher()
        Try
            List_tous.Items.Clear()
            Cmd = New SqlCommand("Select * From Fournisseus", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                Dim Line As ListViewItem = New ListViewItem(New String() {Dr("Nom"), Dr("Prenom"), Dr("type"), Dr("Nom_entreprise"), Dr("Categorie"), Dr("Email"), Dr("Ville"), Dr("Id_Fournisseur")})
                List_tous.Items.Add(Line)
            Loop
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
    Public Sub com_ville()
        Try
            comb_ville.Items.Clear()
            Cmd = New SqlCommand("Select * From Comb_Ville", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                comb_ville.Items.Add(Dr("Ville"))
            Loop
            Dr.Close()
            cnClose()
            If comb_ville.Items.Count > 0 Then
                comb_ville.SelectedIndex = 0
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
    Public Sub Comb_catégori()
        Try
            Comb_catégor.Items.Clear()
            Cmd = New SqlCommand("Select * From Comb_Categ_Fournisseurs", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                Comb_catégor.Items.Add(Dr("Categorie"))
            Loop
            Dr.Close()
            cnClose()
            If Comb_catégor.Items.Count > 0 Then
                Comb_catégor.SelectedIndex = 0
            End If

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub Radio_partc_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Radio_partc.CheckedChanged
        If Radio_partc.Checked = True Then
            txt_nom_entrep.Enabled = False
            txt_nom_interl.Enabled = False

            txt_prenom.Enabled = True
            txt_nom.Enabled = True

            txt_nom_entrep.Clear()
            txt_nom_interl.Clear()

            txt_nom.Select()
            str = "Particulier"
        End If
    End Sub

    Private Sub Radio_entr_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Radio_entr.CheckedChanged
        If Radio_entr.Checked = True Then
            txt_prenom.Enabled = False
            txt_nom.Enabled = False

            txt_nom_entrep.Enabled = True
            txt_nom_interl.Enabled = True

            txt_prenom.Clear()
            txt_nom.Clear()

            txt_nom_entrep.Select()
            str = "Entreprise"
        End If
    End Sub

    Private Sub btn_parcour_ville_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_parcour_ville.Click
        Me.Enabled = False
        Frm_ville.Show()
    End Sub

    Private Sub btn_parcour_catégo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_parcour_catégo.Click
        Frm_Catégorie.Show()
    End Sub

    Private Sub btn_recher_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_recher.Click
        Frm_Recherche_fourniss.Show()
    End Sub


    Private Sub btn_nouv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nouv.Click
        vider()
    End Sub

    Private Sub Frm_Gestion_Fourn_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Afficher()
        Comb_catégori()
        com_ville()
        txt_nom.Select()
        comb_type.SelectedIndex = 0
    End Sub

    Private Sub btn_parcour_photo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_parcour_photo.Click
        With OpenF
            .FileName = ""
            .Title = "Ovrir la Photo"
            .RestoreDirectory = True
            .InitialDirectory = "C:\"
            .Filter = "JPEG Files (*.JPG)|*.JPG"
            .FilterIndex = 1
        End With
        If OpenF.ShowDialog = Windows.Forms.DialogResult.OK Then
            Pic_photo.Image = Image.FromFile(OpenF.FileName)

        End If
    End Sub

    Private Sub btn_supp_phot_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_supp_phot.Click
        Pic_photo.Image = My.Resources.photo
    End Sub

    Private Sub btn_ajouter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ajouter.Click

        If (txt_nom.Text <> "" And txt_prenom.Text <> "") Or (txt_nom_entrep.Text <> "" And txt_nom_interl.Text <> "") Then
            If MsgBox("Vous Voulez Vraiment Ajouter  ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Ajouter ") = MsgBoxResult.Yes Then
                Try
                    Cmd = New SqlCommand("insert into Fournisseus (Nom,Prenom,Adresse,Email,Categorie,Nom_entreprise,Nom_interlocuteur,Ville,Type_Fournisseur,Remarque,type,Photo)" & _
                    "values (@nom,@pren,@adres,@email,@categ,@entre,@interl,@vill,@type,@remarq,@typ,@img)", Con)

                    Cmd.Parameters.Add("@nom", SqlDbType.VarChar).Value = txt_nom.Text
                    Cmd.Parameters.Add("@pren", SqlDbType.VarChar).Value = txt_prenom.Text
                    Cmd.Parameters.Add("@categ", SqlDbType.VarChar).Value = Comb_catégor.SelectedItem
                    Cmd.Parameters.Add("@adres", SqlDbType.VarChar).Value = txt_adresse.Text
                    Cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = txt_mail.Text
                    Cmd.Parameters.Add("@entre", SqlDbType.VarChar).Value = txt_nom_entrep.Text
                    Cmd.Parameters.Add("@interl", SqlDbType.VarChar).Value = txt_nom_interl.Text
                    Cmd.Parameters.Add("@vill", SqlDbType.VarChar).Value = comb_ville.SelectedItem
                    Cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = str
                    Cmd.Parameters.Add("@typ", SqlDbType.VarChar).Value = comb_type.SelectedItem
                    Cmd.Parameters.Add("@remarq", SqlDbType.Text).Value = txt_remarq.Text

                    'اضافة الصورة


                    Dim ms As MemoryStream = New MemoryStream()
                    Pic_photo.Image.Save(ms, ImageFormat.Jpeg)
                    Dim bytBLOBData(ms.Length - 1) As Byte
                    ms.Position = 0
                    ms.Read(bytBLOBData, 0, ms.Length)
                    Dim prm As New SqlParameter("@img", SqlDbType.VarBinary, _
                    bytBLOBData.Length, ParameterDirection.Input, False, _
                    0, 0, Nothing, DataRowVersion.Current, bytBLOBData)
                    Cmd.Parameters.Add(prm)

                    open()
                    Cmd.ExecuteNonQuery()
                    cnClose()
                    ' num استخراج
                    Try
                        Cmd = New SqlCommand("Select * From Fournisseus", Con)
                        open()
                        Dr = Cmd.ExecuteReader()
                        If Dr.HasRows Then
                            Do While Dr.Read()
                                num = Dr("Id_Fournisseur")
                            Loop
                        End If

                        Dr.Close()
                        cnClose()

                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try

                    'الهاتف

                    Cmd = New SqlCommand("insert into Fournisseurs_Tele values (@numM,@tele,@type)", Con)
                    Cmd.Parameters.Add("@numM", SqlDbType.Int).Value = num
                    Cmd.Parameters.Add("@tele", SqlDbType.VarChar).Value = txt_portabl.Text
                    Cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = "Portable"

                    open()
                    Cmd.ExecuteNonQuery()
                    cnClose()

                    Cmd = New SqlCommand("insert into Fournisseurs_Tele values (@numM,@tele,@type)", Con)
                    Cmd.Parameters.Add("@numM", SqlDbType.Int).Value = num
                    Cmd.Parameters.Add("@tele", SqlDbType.VarChar).Value = txt_fix.Text
                    Cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = "Fix"

                    open()
                    Cmd.ExecuteNonQuery()
                    cnClose()


                    Cmd = New SqlCommand("insert into Fournisseurs_Tele values (@numM,@tele,@type)", Con)
                    Cmd.Parameters.Add("@numM", SqlDbType.Int).Value = num
                    Cmd.Parameters.Add("@tele", SqlDbType.VarChar).Value = txt_fax.Text
                    Cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = "Fax"

                    open()
                    Cmd.ExecuteNonQuery()
                    cnClose()



                    MsgBox("Fournisseur Ajouté", MsgBoxStyle.Information, "Réussi !")
                    vider()
                    Afficher()

                Catch ex As Exception

                    MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                    cnClose()
                End Try
            End If

        Else
            MsgBox("Entrez les Données", MsgBoxStyle.Critical, "Erreur !")
        End If
    End Sub

    Private Sub btn_supp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_supp.Click


        Dim n As Integer
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(7).Text)
                Next
                If MsgBox("Voulez Vous Vraiment Supprimer ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Supprission !") = MsgBoxResult.Yes Then
                    Try
                        Cmd = New SqlCommand("Delete From Fournisseurs_Tele Where Id_Fournisseur =" & n, Con)
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()

                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try
                    Try
                        Cmd = New SqlCommand("Delete From Fournisseus Where Id_Fournisseur =" & n, Con)
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()
                        Afficher()
                        vider()
                        modif = 0
                        MsgBox("Suppresion effectué !", MsgBoxStyle.Information, "Terminer")
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try
                End If
            Else
                MsgBox("Vous n'avez selectionner aucun Fournisseur !", MsgBoxStyle.Critical, "Réssayer !")
            End If
        End With

    End Sub

    Private Sub List_tous_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles List_tous.SelectedIndexChanged
        vider()
        Dim n As Integer
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(7).Text)
                    modif = CInt(.Items(.SelectedIndices(i)).SubItems(7).Text)
                Next
            End If
        End With

        Try
            Cmd = New SqlCommand("Select * From Fournisseus where Id_Fournisseur =" & n, Con)
            open()
            Dr = Cmd.ExecuteReader()
            Dr.Read()
            txt_nom.Text = Dr("Nom")
            txt_prenom.Text = Dr("Prenom")
            Comb_catégor.Text = Dr("Categorie")
            txt_adresse.Text = Dr("Adresse")
            txt_mail.Text = Dr("Email")
            txt_nom_entrep.Text = Dr("Nom_entreprise")
            txt_nom_interl.Text = Dr("Nom_interlocuteur")
            txt_remarq.Text = Dr("Remarque")
            comb_ville.Text = Dr("Ville")
            comb_type.Text = Dr("type")
            If Dr("Type_Fournisseur") = "Particulier" Then
                Radio_partc.Checked = True
            ElseIf Dr("Type_Fournisseur") = "Entreprise" Then
                Radio_entr.Checked = True
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception

            'MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try

        'جلب الصورة
        Cmd = New SqlCommand("SELECT Photo FROM Fournisseus Where Id_Fournisseur =" & n, Con)
        Da = New SqlDataAdapter(Cmd)
        Ds = New DataSet()
        Da.Fill(Ds, "Fournisseus")
        Dim c As Integer = Ds.Tables("Fournisseus").Rows.Count
        If c > 0 Then
            Dim bytBLOBData() As Byte = _
                Ds.Tables("Fournisseus").Rows(c - 1)("Photo")
            Dim stmBLOBData As New MemoryStream(bytBLOBData)
            Pic_photo.Image = Image.FromStream(stmBLOBData)
        End If

        Try
            Cmd = New SqlCommand("Select Tele,typ From Fournisseurs_Tele Where Id_Fournisseur=" & n, Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                If Dr("typ") = "Portable" Then
                    txt_portabl.Text = Dr("Tele")
                ElseIf Dr("typ") = "Fix" Then
                    txt_fix.Text = Dr("Tele")
                ElseIf Dr("typ") = "Fax" Then
                    txt_fax.Text = Dr("Tele")
                End If
            Loop
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub btn_modif_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_modif.Click
        If modif <> 0 Then

            If (txt_nom.Text <> "" And txt_prenom.Text <> "") Or (txt_nom_entrep.Text <> "" And txt_nom_interl.Text <> "") Then
                If MsgBox("Vous Voulez Vraiment Modifier ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Modifier !") = MsgBoxResult.Yes Then
                    Try
                        Cmd = New SqlCommand("update Fournisseus set Nom=@nom,Prenom=@pren,Categorie=@Categorie,Adresse=@adres,Email=@email,Nom_entreprise=@entre,Nom_interlocuteur=@interl,Ville=@vill,Type_Fournisseur=@type,Remarque=@remarq,type=@typ,Photo=@img" & _
                        " Where Id_Fournisseur=" & modif, Con)

                        Cmd.Parameters.Add("@nom", SqlDbType.VarChar).Value = txt_nom.Text
                        Cmd.Parameters.Add("@pren", SqlDbType.VarChar).Value = txt_prenom.Text
                        Cmd.Parameters.Add("@Categorie", SqlDbType.VarChar).Value = Comb_catégor.SelectedItem
                        Cmd.Parameters.Add("@adres", SqlDbType.VarChar).Value = txt_adresse.Text
                        Cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = txt_mail.Text
                        Cmd.Parameters.Add("@entre", SqlDbType.VarChar).Value = txt_nom_entrep.Text
                        Cmd.Parameters.Add("@interl", SqlDbType.VarChar).Value = txt_nom_interl.Text
                        Cmd.Parameters.Add("@vill", SqlDbType.VarChar).Value = comb_ville.SelectedItem
                        Cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = str
                        Cmd.Parameters.Add("@typ", SqlDbType.VarChar).Value = comb_type.SelectedItem
                        Cmd.Parameters.Add("@remarq", SqlDbType.Text).Value = txt_remarq.Text

                        'اضافة الصورة


                        Dim ms As MemoryStream = New MemoryStream()
                        Pic_photo.Image.Save(ms, ImageFormat.Jpeg)
                        Dim bytBLOBData(ms.Length - 1) As Byte
                        ms.Position = 0
                        ms.Read(bytBLOBData, 0, ms.Length)
                        Dim prm As New SqlParameter("@img", SqlDbType.VarBinary, _
                        bytBLOBData.Length, ParameterDirection.Input, False, _
                        0, 0, Nothing, DataRowVersion.Current, bytBLOBData)
                        Cmd.Parameters.Add(prm)


                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()

                        Cmd = New SqlCommand("Update Fournisseurs_Tele set Tele=@tele Where Typ='Fax' and Id_Fournisseur=" & modif, Con)
                        Cmd.Parameters.Add("@tele", SqlDbType.VarChar).Value = txt_fax.Text
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()

                        Cmd = New SqlCommand("Update Fournisseurs_Tele set Tele=@tele Where Typ='Fix' and Id_Fournisseur=" & modif, Con)
                        Cmd.Parameters.Add("@tele", SqlDbType.VarChar).Value = txt_fix.Text
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()

                        Cmd = New SqlCommand("Update Fournisseurs_Tele set Tele=@tele Where Typ='Portable' and Id_Fournisseur=" & modif, Con)
                        Cmd.Parameters.Add("@tele", SqlDbType.VarChar).Value = txt_portabl.Text
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()

                        Afficher()
                        vider()
                        modif = 0
                        MsgBox("Modification a Réussi ", MsgBoxStyle.Information, "Réussi !")
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try


                End If
            Else
                MsgBox("Vous n'avez selectionner aucun Fournisseur !", MsgBoxStyle.Critical, "Réssayer !")
            End If
        End If
        modif = 0
    End Sub
End Class